﻿using SqlPerfomance.V2.DomainModel.Enums;
using SqlPerfomance.V2.DomainModel.Models;
using SqlPerfomance.V2.DomainModel.NodeTree;
using SqlPerfomance.V2.DomainModel.NodeTree.Interfaces;
using SqlPerfomance.V2.Helpers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Threading.Tasks;

namespace SqlPerfomance.V2.DAL
{
    public class NestedSetTreeRepository : RealDataRepository<NestedSetTreeNode>
	{
        private DatabaseLogger logger;
        public NestedSetTreeRepository()
        {
            logger = new DatabaseLogger();
        }

		private int _rght = 0; 
        public override ITree<NestedSetTreeNode> CreateTree(INode<CommonTreeNode> commonTree)
        {
			ITree<NestedSetTreeNode> nsTree = NodeTree<NestedSetTreeNode>.NewTree();
			//nsTree.Inserted += NsTreeOnInserted;

			ITree<NestedSetTreeNode> nsRoot = nsTree;
			//INode<NestedSetTreeNode> nsTop = nsRoot.AddChild(nsTopNode);
			INode<NestedSetTreeNode> nst = nsRoot.Root;
			//var t = commonTree.Root.DirectChildren.Nodes.First().Nodes.Skip(1).First();
			INode<CommonTreeNode> topNode = commonTree.Root.DirectChildren.Nodes.First();

			AddItem2(nst, topNode);

			//nsTopNode.Rgt = ++_rght;
			//nsTree.Inserted -= NsTreeOnInserted;
			//DatabaseInitHelper.CreateStoredProcedures();
			//UpdateNsTreeSides();
			//UpdateRght(nsTree);
			return nsTree;
        }

		#region CreateNestedSetTree

		public IEnumerable<NestedSetTreeExtensionNode> GetNestedSetData(ConnectionStrings connectionString, string tableName)
		{
			string query = string.Empty;
			switch (connectionString)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(@"SELECT *
												,0 as parent_id
											FROM {0}
											LIMIT 1
											UNION ALL
											SELECT node.*
												,parent.Id
													FROM {0} node, {0} parent
													WHERE node.lft BETWEEN parent.lft AND parent.rgt
													AND node.level = parent.level + 1 ", tableName);
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(@"SELECT TOP 1 *
												,0 as parent_id
											FROM {0}
											UNION ALL
											SELECT node.*
												,parent.Id
													FROM {0} node, {0} parent
													WHERE node.lft BETWEEN parent.lft AND parent.rgt
													AND node.level = parent.level + 1 
													", tableName);
					break;
			}
			using (var conn = GetDbConnection(connectionString))
			{
				conn.Open();
				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					var reader = cmd.ExecuteReader();
					List<NestedSetTreeExtensionNode> nodes = new List<NestedSetTreeExtensionNode>();
					while (reader.Read())
					{
						nodes.Add(new NestedSetTreeExtensionNode
						{
							Id = reader.GetInt32(0),
							Name = reader.GetString(1),
							Lft = reader.GetInt32(2),
							Rgt = reader.GetInt32(3),
							Level = reader.GetInt32(4),
							ParentId = reader.GetInt32(5)
						});
					}
					return nodes;
				}
			}
		}

        private void UpdateRght(ITree<NestedSetTreeNode> nsTree)
        {
            foreach (var node in nsTree.Values)
            {
                node.Rgt = _rght;
                _rght--;
            }
        }

		private void AddItem2(INode<NestedSetTreeNode> nsNode, INode<CommonTreeNode> commonTree)
		{
			var n = new NestedSetTreeNode
			{
				Id = commonTree.Data.Id,
				Name = commonTree.Data.Name,
				Level = commonTree.Depth,
				Lft = ++_rght
			};
			var node = nsNode.AddChild(n);
			//var children = commonTree.Nodes.Where(t => t.Data.Parent_Id == nsNode.Data.Id);
			foreach (var child in commonTree.DirectChildren.Nodes)
			{
					AddItem2(node, child);
			}
			n.Rgt = ++_rght;
		}

		private void AddItem(INode<NestedSetTreeNode> nsNode, INode<CommonTreeNode> commonTree)
		{
			var child = new NestedSetTreeNode();
			foreach (var node in commonTree.DirectChildren.Nodes)
			{				
				child.Id = node.Data.Id;
				child.Name = node.Data.Name;
				child.Level = node.Depth;
				child.Lft = ++_rght;
				var nsChild = nsNode.AddChild(child);

				//((IList<INode<NestedSetTreeNode>>)NsTree.Nodes).Add(child);

				AddItem(nsChild, node);
			}
			child.Rgt = ++_rght;
		}

		private void NsTreeOnInserted(object sender, NodeTreeInsertEventArgs<NestedSetTreeNode> nodeTreeInsertEventArgs)
		{
			NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.SqlServerConnectionString);
			//NodeTreeSave(nodeTreeInsertEventArgs.Node, ConnectionStrings.MySqlConnectionString);
		}
		#endregion

		#region UpdateNsTreeSides
		
		#region StoredProcCalls

		private const string NsTreeUpdateSidesExec = @"EXEC NsTreeUpdateProc{0}";
		private const string NsTreeUpdateSidesCall = @"CALL NsTreeUpdateProc()";
		#endregion

		private void UpdateNsTreeSides()
		{
			using (DbConnection connection = GetDbConnection(ConnectionStrings.SqlServerConnectionString))
			{
				connection.Open();
				using (DbCommand command = connection.CreateCommand())
				{
					command.CommandText = string.Format(NsTreeUpdateSidesExec, GetNodeTreeTableName(typeof(NestedSetTreeNode)));
					command.CommandTimeout = 3 * short.MaxValue;
					command.ExecuteNonQuery();
				}
			}
			//using (DbConnection connection = GetDbConnection(ConnectionStrings.MySqlConnectionString))
			//{
			//	connection.Open();
			//	using (DbCommand command = connection.CreateCommand())
			//	{
			//		command.CommandText = NsTreeUpdateSidesCall;
			//		command.CommandTimeout = 10800;
			//		command.ExecuteNonQuery();
			//	}
			//}
		}


		#endregion

		#region LoadNsTreeFromDb

		#region SqlQueries
		/// <summary>
		/// Запрос для получения дочерних узлов (возварщает сам узел и его дочерние узлы)
		///{0} - Lft элемента, {1} - таблица nsTree
		/// </summary>
		private const string NsTreeQuery = @"
	SELECT Child.id, Child.Name, Child.LFT, Child.RGT, Child.Level
FROM {1} AS Parent, {1} AS Child
WHERE
	Child.LFT BETWEEN Parent.LFT AND Parent.RGT
	AND NOT EXISTS (   
		SELECT *
		FROM {1} AS Mid
		WHERE Mid.LFT BETWEEN Parent.LFT AND Parent.RGT
     			AND Child.LFT BETWEEN Mid.LFT AND Mid.RGT
			AND Mid.Name NOT IN (Parent.Name, Child.Name)
	) 
	AND Parent.LFT = {0}";

		/// <summary>
		/// {0} - Таблица nsTree
		/// </summary>
		private const string NsTreeTopNodeQuery = @"
SELECT {1} node.id AS Id
, node.name AS Name
, node.Lft AS Lft
, node.Rgt AS Rgt
, node.Level AS Lvl
FROM
{0} AS node
WHERE node.LFT = 1--node.parent_id IS NULL
{2}";

		private const string NsTreeSelectQuery = @"SELECT * FROM {0}";
#endregion

		#region SqliteQueries

		private const string SqliteTableName = "NestedSetTreeTable";
		private const string SqliteTableQuery =
			"create table {0}(id int primary key, Level int, Lft int, Rgt int, Name text, Parent_id int);";
		private const string SqliteInsertQuery = 
			"insert into {0}(id, Level, Lft, Rgt, Name, Parent_id) values({1}, {2}, {3}, {4}, '{5}', {6});";

		private const string SqliteIndexQuery = @"CREATE INDEX `indx_NestedSetTreeTable_Lft` ON `{0}` (`Lft` ASC);
CREATE INDEX `indx_NestedSetTreeTable_Rgt` ON `{0}` (`Rgt` ASC);";

		#endregion
		
		private ITree<NestedSetTreeNode> NsTree { get; set; }

		public ITree<NestedSetTreeNode> GetNestedSetTree(ConnectionStrings conStr)
		{
			//Process process = Process.GetCurrentProcess();
			//process.PriorityClass = ProcessPriorityClass.Normal;
			LoadNsTree(conStr, GetNodeTreeTableName(typeof(NestedSetTreeNode)));
			var result = NsTree;
			NsTree = null;
			//process.PriorityClass = ProcessPriorityClass.Normal;
			return result;
		}

		private void LoadNsTree(ConnectionStrings conStr, string tableName)
		{
			NsTree = NodeTree<NestedSetTreeNode>.NewTree();
			var topNode = NsTree.AddChild(GetTopNodeOfNestedSetTree(conStr, tableName));
			
			var allNodes = LoadNsTreeFromSqlDatabase(conStr, tableName);

			var connection = new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
			connection.Open();

			InsertNsTreeIntoSqliteDatabase(connection, allNodes);

			AddNsTreeChildNodes(connection, topNode);
			connection.Close();
		}

		private IEnumerable<NestedSetTreeNode> LoadNsTreeFromSqlDatabase(ConnectionStrings conStr, string tableName)
		{
			var nsTreeNodes = new List<NestedSetTreeNode>();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = string.Format(NsTreeSelectQuery, tableName);
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						var node = new NestedSetTreeNode
						{
							Id = (int) reader["Id"],
							Level = (int) reader["Level"],
							Lft = (int) reader["Lft"],
							Rgt = (int) reader["Rgt"],
							Name = (string) reader["Name"],
							//Parent_Id = reader["Parent_Id"] as int?
						};
						nsTreeNodes.Add(node);
					}
				}
			}
			return nsTreeNodes;
		}

		private void InsertNsTreeIntoSqliteDatabase(SQLiteConnection connection, IEnumerable<NestedSetTreeNode> allNodes)
		{
			using (var sqliteCreateTableCmd = new SQLiteCommand(connection))
			{
				sqliteCreateTableCmd.CommandText = string.Format(SqliteTableQuery, SqliteTableName);
				sqliteCreateTableCmd.ExecuteNonQuery();
			}

			using (var insertQuery = new SQLiteCommand(connection))
			{
				foreach (var node in allNodes)
				{
					insertQuery.CommandText = string.Format(
						SqliteInsertQuery,
						SqliteTableName,
						node.Id,
						node.Level,
						node.Lft,
						node.Rgt,
						node.Name);//, (node.Parent_Id ?? (object)"null"));
					insertQuery.ExecuteNonQuery();
				}
			}

			using (var command = new SQLiteCommand(connection))
			{
				command.CommandText = string.Format(SqliteIndexQuery, SqliteTableName);
				command.ExecuteNonQuery();
			}
		}

		private NestedSetTreeNode GetTopNodeOfNestedSetTree(ConnectionStrings conStr, string tableName)
		{
			string query = string.Empty;
			switch (conStr)
			{
				case ConnectionStrings.MySqlConnectionString:
					query = string.Format(NsTreeTopNodeQuery, tableName, string.Empty, "LIMIT 1");
					break;
				case ConnectionStrings.SqlServerConnectionString:
					query = string.Format(NsTreeTopNodeQuery, tableName, "TOP 1", string.Empty);
					break;
			}
			var nsTree = new NestedSetTreeNode();
			using (DbConnection conn = GetDbConnection(conStr))
			{
				conn.Open();
				using (DbCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = query;
					cmd.CommandTimeout = 360;
					DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
					while (reader.Read())
					{
						nsTree.Id = (int)reader["Id"];
						var tmp = reader["Lvl"];
						nsTree.Level = Convert.ToInt32(reader["Lvl"]);
						nsTree.Lft = (int)reader["Lft"];
						nsTree.Rgt = (int)reader["Rgt"];
						nsTree.Name = (string)reader["Name"];
					}
				}
			}
			return nsTree;
		}

		private void AddNsTreeChildNodes(SQLiteConnection conn,
			INode<NestedSetTreeNode> tree)
		{
			var childrens = new List<NestedSetTreeNode>();
			using (SQLiteCommand cmd = conn.CreateCommand())
			{
				cmd.CommandText = string.Format(NsTreeQuery, tree.Data.Lft,
					SqliteTableName);
				var reader = cmd.ExecuteReader();
				while (reader.Read())
				{
					var node = new NestedSetTreeNode
					{
						Id = (int)reader["Id"],
						Level = (int)reader["Level"],
						Lft = (int)reader["Lft"],
						Rgt = (int)reader["Rgt"],
						Name = (string)reader["Name"]
					};
					childrens.Add(node);
				}
			}
			if (childrens.Remove(tree.Data))
			{
				if (childrens.Count != 0)
				{
					Parallel.ForEach(childrens, child =>
					{
						var addedChild = tree.AddChild(child);
						AddNsTreeChildNodes(conn, addedChild);
					});
				}

			}
		}

		#endregion

		#region Tests
		private IEnumerable<NestedSetTreeNode> ReadNodes(string sql, ConnectionStrings connectionString)
		{
			using(var connection = GetDbConnection(connectionString))
			{
				connection.Open();
				using (var cmd = connection.CreateCommand())
				{
					cmd.CommandText = sql;
					var reader = cmd.ExecuteReader();
					var nodes = new List<NestedSetTreeNode>();
					while(reader.Read())
					{
						nodes.Add(new NestedSetTreeNode()
							{
								Id = (int)reader["Id"],
								Name = (string)reader["name"],
								Lft = (int)reader["Lft"],
								Rgt = (int)reader["Rgt"],
								Level = (int)reader["Level"]
							});
					}
					return nodes;
				}
			}
		}

		public IEnumerable<NestedSetTreeNode> GetNsData(ConnectionStrings connectionString, string tableName)
		{
			string sql = string.Format("SELECT * FROM {0}", tableName);
            logger.Log(connectionString, sql, "NS_LoadTreeFromSqlDatabase");
			return ReadNodes(sql, connectionString);
		}

		public IEnumerable<NestedSetTreeNode> SelectPathById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT parent.*
											FROM {0} node, {0} parent
											WHERE node.lft BETWEEN parent.lft AND parent.rgt
											AND node.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "NS_SelectPathById");
			return ReadNodes(sql, connectionString);
		}

		public IEnumerable<NestedSetTreeNode> SelectBranchById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT node.*
										FROM {0} node, {0} parent
										WHERE node.lft >= parent.lft AND node.rgt <= parent.rgt
										AND parent.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "NS_SelectBranchById");
			return ReadNodes(sql, connectionString);
		}

		public IEnumerable<NestedSetTreeNode> SelectParentById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT parent.*
										FROM {0} node, {0} parent
										WHERE node.lft BETWEEN parent.lft AND parent.rgt
										AND parent.level = node.level - 1 AND node.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "NS_SelectParentById");
			return ReadNodes(sql, connectionString);
		}

		public IEnumerable<NestedSetTreeNode> SelectChildrenById(ConnectionStrings connectionString, string tableName, int id)
		{
			string sql = string.Format(@"SELECT node.*
										FROM {0} node, {0} parent
										WHERE node.lft BETWEEN parent.lft AND parent.rgt
										AND node.level = parent.level + 1 and parent.id = {1}", tableName, id);
            logger.Log(connectionString, sql, "NS_SelectChildrenById");
			return ReadNodes(sql, connectionString);
		}

		public void AddChildrenNode(ConnectionStrings connectionString, string tableName, int parentId)
		{
			string randomName = RandomStrings.GetRandomString(6);
            string sql = string.Empty;
            if (connectionString == ConnectionStrings.SqlServerConnectionString)
            {
                sql = string.Format(@"DECLARE @parent_id INT
										,@treeRight INT
										,@nodeLevel INT
		
									SET @parent_id = {0}

									SELECT @treeRight = rgt FROM {1} WHERE id = @parent_id

									SELECT @nodeLevel = parent.level + 1 FROM {1} node, {1} parent
									WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.level = node.level - 1 AND node.id = @parent_id

									UPDATE {1} SET rgt = rgt + 2 WHERE rgt > @treeRight
									UPDATE {1} SET lft = lft + 2 WHERE lft > @treeRight

									INSERT INTO {1} (name, lft, rgt, Level) VALUES('{2}', @treeRight + 1, @treeRight + 2, @nodeLevel)", parentId, tableName, randomName);
            }
            else
            {
                sql = string.Format(@"SELECT @treeRight := rgt FROM {0} WHERE id = {1};
                        SELECT @nodeLevel := parent.level + 1 FROM {0} node, {0} parent
                        WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.level = node.level - 1 AND node.id = {1};

                        UPDATE {0} SET rgt = rgt + 2 WHERE rgt > @treeRight;
                        UPDATE {0} SET lft = lft + 2 WHERE lft > @treeRight;
                        INSERT INTO {0} (id, name, lft, rgt, level) VALUES(0, '{2}', @treeRight + 1, @treeRight + 2, @nodeLevel);", tableName, parentId, randomName);
            }
            logger.Log(connectionString, sql, "NS_AddNode");
			ExecuteQuery(sql, connectionString);
		}

		public void RemoveNode(ConnectionStrings connectionString, string tableName, int id)
		{
            string sql = string.Empty;
            if (connectionString == ConnectionStrings.SqlServerConnectionString)
            {
                sql = string.Format(@"DECLARE @id INT
											, @treeLeft INT
											, @treeRight INT
											, @treeWidth INT

										SET @id = {0}

										SELECT @treeLeft = lft, @treeRight = rgt, @treeWidth = rgt - lft + 1 FROM {1} WHERE id = @id
										DELETE FROM {1} WHERE lft BETWEEN @treeLeft AND @treeRight
										UPDATE {1} SET rgt = (rgt - @treeWidth) WHERE rgt > @treeRight
										UPDATE {1} SET lft = (lft - @treeWidth) WHERE lft > @treeRight", id, tableName);
            }
            else 
            {
                sql = string.Format(@"SELECT @treeLeft := lft, @treeRight := rgt, @treeWidth := rgt - lft + 1 FROM {0} WHERE id = {1};
                            DELETE FROM {0} WHERE lft BETWEEN @treeLeft AND @treeRight;
                            UPDATE {0} SET rgt = rgt - @treeWidth WHERE rgt > @treeRight;
                            UPDATE {0} SET lft = lft - @treeWidth WHERE lft > @treeRight;", tableName, id);
            }
            logger.Log(connectionString, sql, "NS_RemoveNode");
			ExecuteQuery(sql, connectionString);
		}
		#endregion

        public void MoveNode(ConnectionStrings connectionString, int id, int parentId)
        {
			string sql = string.Empty;
			if (connectionString == ConnectionStrings.SqlServerConnectionString)
			{
				sql = string.Format(@"EXEC [dbo].[MOVE_NODE_NS]
												@node_id = {0},
												@parent_id = {1}", id, parentId);
			}
			else
			{
				sql = string.Format(@"CALL MOVE_NODE_NS({0}, {1})", id, parentId);
			}
			logger.Log(connectionString, sql, "NS_MoveNode");
			ExecuteQuery(sql, connectionString);
        }
    }
}
